
if exists (select 1 from sysobjects where name = 'vw_person' and type = 'V')
begin
   drop view vw_person
   print 'View: vw_person deleted ...'
end
go
create view vw_person
as
   select 
      prsid  = p.prsid,
   nachname  = p.nachname,
    vorname  = p.vorname, 
    strasse  = p.strasse + ' ' + p.strassennr,
   postfach  = p.postfach,
        PLZ  = p.plz,
        ort  = p.ort,
     telnrp  = p.telnrp,
     telnrn  = p.telnrn,
      email  = p.email,
    prozent  = a.prozent, 
abteilungid  = abt.abteilungid,
  abteilung  = abt.name,
anstellungid = ans.anstellungid,
 anstellung  = ans.name
   from person p 
  inner join personalabteilung pa 
     on p.prsid = pa.prsid 
   left outer join abteilung abt
     on abt.abteilungid = pa.abteilungid
  inner join arbeitspensum a 
     on a.prsid = p.prsid 
    and a.status = 1 
   left outer join personanstellung pans
     on pans.prsid = p.prsid
   left outer join anstellung ans
     on ans.anstellungid = pans.anstellungid

go


if exists (select 1 from sysobjects where name = 'vw_personferien' and type = 'V')
begin
   drop view vw_personferien
   print 'View: vw_personferien deleted ...'
end
go
create view vw_personferien
as
   select 
      prsid  = p.prsid,
   nachname  = p.nachname,
    vorname  = p.vorname, 
    strasse  = p.strasse + ' ' + p.strassennr,
   postfach  = p.postfach,
        PLZ  = p.plz,
        ort  = p.ort,
     telnrp  = p.telnrp,
     telnrn  = p.telnrn,
      email  = p.email,
   ferienid = f.ferienid,
  ferienvon = f.von,
  ferienbis = f.bis
     from person p
     left outer join ferien f
       on f.prsid = p.prsid
go


if exists (select 1 from sysobjects where name = 'vw_abteilung' and type = 'V')
begin
   drop view vw_abteilung
   print 'View: vw_abteilung deleted ...'
end
go
create view vw_abteilung
as
   select 
    a.Abteilungid,
    a.name,
    b.kurztext,
    b.Langtext,
    b.beschrid
   from abteilung a
   left outer join beschreibung b
   on b.beschrid = a.beschrid
go


if exists (select 1 from sysobjects where name = 'vw_dienst' and type = 'V')
begin
   drop view vw_dienst
   print 'View: vw_dienst deleted ...'
end
go
create view vw_dienst
as
   select 
    d.dienstid,
    d.name,
    b.kurztext,
    b.Langtext,
    b.beschrid
   from dienst d
   left outer join beschreibung b
   on b.beschrid = d.beschrid
go


if exists (select 1 from sysobjects where name = 'vw_initgrid' and type = 'V')
begin
   drop view vw_initgrid
   print 'View: vw_initgrid deleted ...'
end
go
create view vw_initgrid
as
     select top 1000
          d.planid, 
          d.prsid, 
          p.nachname, 
          p.vorname, 
          a.prozent 
       from dienstplan d 
 inner join person p 
         on p.prsid = d.prsid 
 inner join arbeitspensum a 
         on a.prsid = p.prsid 
        and a.status = 1 
   group by d.prsid, d.planid, p.nachname, p.vorname, a.prozent
   order by d.planid
go